package com.hulu.yec.sys.yecclass.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtil {
	final static String DRIVER_CLASS = "com.mysql.jdbc.Driver";
	final static String URL = "jdbc:mysql://localhost:3306/permissiondb";
	final static String USERNAME = "root";
	final static String PASSWORD = "root";

	private static Connection getConnection() {
		Connection conn = null;

		try {
			Class.forName(DRIVER_CLASS);
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return conn;
	}

	/**
	 * 执行简单SQL的增，删，改
	 * 
	 * @param sql
	 *            调用处传入SQL命令
	 * @param parameters
	 *            与SQL命令对应的SQL参数数组
	 * @return 返回操作受影响的行数
	 */
	public static int executeUpdate(String sql, Object... parameters) {
		int row = 0;

		Connection conn = getConnection();
		PreparedStatement pst = null;

		try {
			pst = conn.prepareStatement(sql);
			// 判断是否设置参数
			if (parameters != null && parameters.length > 0) {
				for (int i = 0; i < parameters.length; i++) {
					pst.setObject(i + 1, parameters[i]);
				}
			}

			row = pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeObject(pst, conn);
		}

		return row;
	}

	/**
	 * 执行查询SQL指令操作
	 * 
	 * @param sql
	 *            调用处传入SQL命令
	 * @param parameters
	 *            与SQL命令对应的SQL参数数组
	 * @return 返回才查询结果集合
	 */
	public static List<Map<String, Object>> executeQuery(String sql,
			Object... parameters) {
		List<Map<String, Object>> table = new ArrayList<>();

		Connection conn = getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			pst = conn.prepareStatement(sql);
			// 判断是否设置参数
			if (parameters != null && parameters.length > 0) {
				for (int i = 0; i < parameters.length; i++) {
					pst.setObject(i + 1, parameters[i]);
				}
			}

			rs = pst.executeQuery();

			if (rs != null) {
				// 把结果集转换为虚拟表
				ResultSetMetaData rsd = rs.getMetaData();
				// 获取表格中的列数
				int count = rsd.getColumnCount();

				while (rs.next()) {
					// 创建一个存储当前遍历行每个单元格数据的map集合对象
					Map<String, Object> row = new HashMap<>();

					for (int i = 0; i < count; i++) {
						row.put(rsd.getColumnName(i + 1),
								rs.getObject(rsd.getColumnName(i + 1)));
					}

					// 把当前遍历行组成集合对象存储到table集合中
					table.add(row);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeObject(rs, pst, conn);
		}

		return table;
	}

	/**
	 * 关闭资源
	 * 
	 * @param parameters
	 */
	private static void closeObject(Object... parameters) {
		try {
			if (parameters != null && parameters.length > 0) {
				for (Object obj : parameters) {
					if (obj instanceof ResultSet) {
						((ResultSet) obj).close();
					}

					if (obj instanceof PreparedStatement) {
						((PreparedStatement) obj).close();
					}

					if (obj instanceof Connection) {
						Connection conn = (Connection) obj;
						if (conn != null && !conn.isClosed()) {
							conn.close();
							conn = null; // 销毁内存连接对象
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
